package io.cloud.tec.ant_design_vue_pro.biz;

import io.cloud.tec.ant_design_vue_pro.dao.AccountLoginInfoMapper;
import io.cloud.tec.ant_design_vue_pro.dto.LoginReqDTO;
import io.cloud.tec.ant_design_vue_pro.entity.AccountLoginInfo;
import io.cloud.tec.project.model.PageResult;
import io.cloud.tec.project.util.PageUtils;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.concurrent.*;

@Slf4j
@Service
@AllArgsConstructor
@Configuration
public class AccountLoginInfoService {

    AccountLoginInfoMapper mapper;

    public void validateLoginInfo(LoginReqDTO loginReqDTO) {
        AccountLoginInfo accountLoginInfo = mapper.selectAccountLoginInfoByLoginReq();
    }

    public PageResult<AccountLoginInfo> page(LoginReqDTO loginReqDTO) {
        PageUtils.page(1, 100);
        List<AccountLoginInfo> page = mapper.page();
        return PageUtils.toPage(page);
    }

    public List<AccountLoginInfo> pageList(LoginReqDTO loginReqDTO) {
        PageUtils.page(1, 100);
        List<AccountLoginInfo> page = mapper.page();
        return page;
    }

    public List<AccountLoginInfo> list(LoginReqDTO loginReqDTO) {
        List<AccountLoginInfo> page = mapper.page();
        return page;
    }
    /**************************************************************************************/
    /*                    方案一：事务不生效，所有sql以单事务执行，数据未回滚                      */

    /**************************************************************************************/
    /*
    Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c13a8e6]
JDBC Connection [HikariProxyConnection@396376158 wrapping com.mysql.cj.jdbc.ConnectionImpl@205b5b5c] will be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
==> Parameters: 99(Integer)
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c13a8e6]
2023-08-02 21:49:13.214  INFO 11896 --- [nio-8081-exec-2] i.c.t.a.biz.AccountLoginInfoService      : (AccountLoginInfoService.java:65) 新插入的ID为99
Creating a new SqlSession
Creating a new SqlSession
Creating a new SqlSession
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c13a8e6]
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5fcf724c] was not registered for synchronization because synchronization is not active
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3be898ce] was not registered for synchronization because synchronization is not active
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@a1bdc23] was not registered for synchronization because synchronization is not active
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c13a8e6]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c13a8e6]
JDBC Connection [HikariProxyConnection@1542520173 wrapping com.mysql.cj.jdbc.ConnectionImpl@205b5b5c] will not be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
==> Parameters: 2(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5fcf724c]
JDBC Connection [HikariProxyConnection@237263288 wrapping com.mysql.cj.jdbc.ConnectionImpl@205b5b5c] will not be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
2023-08-02 21:49:13.378  INFO 11896 --- [      Thread-12] i.c.t.a.biz.AccountLoginInfoService      : (AccountLoginInfoService.java:65) 新插入的ID为2
==> Parameters: 3(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@a1bdc23]
JDBC Connection [HikariProxyConnection@1071061867 wrapping com.mysql.cj.jdbc.ConnectionImpl@205b5b5c] will not be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
2023-08-02 21:49:13.452  INFO 11896 --- [      Thread-13] i.c.t.a.biz.AccountLoginInfoService      : (AccountLoginInfoService.java:65) 新插入的ID为3
==> Parameters: 4(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3be898ce]
2023-08-02 21:49:13.526  INFO 11896 --- [      Thread-14] i.c.t.a.biz.AccountLoginInfoService      : (AccountLoginInfoService.java:65) 新插入的ID为4
Exception in thread "Thread-14" java.lang.ArithmeticException: / by zero
	at io.cloud.tec.ant_design_vue_pro.biz.AccountLoginInfoService.insertOnlyPK(AccountLoginInfoService.java:67)
	at io.cloud.tec.ant_design_vue_pro.biz.AccountLoginInfoService$1.run(AccountLoginInfoService.java:52)
	at java.lang.Thread.run(Thread.java:748)
     */
    @Transactional
    public void batchInsert1() {
        insertOnlyPK1(99);
        for (int i = 2; i < 5; i++) {
            int finalI = i;
            Runnable runnable = new Runnable() {
                @Override
                public void run() {
                    insertOnlyPK1(finalI);
                }
            };

            new Thread(runnable).start();
        }
    }

    @Transactional
    public void insertOnlyPK1(Integer id) {
        AccountLoginInfo insertData = new AccountLoginInfo();
        insertData.setId(id);
        Integer newId = mapper.insertOnlyPK(insertData);
        log.info("新插入的ID为{}", id);
        if (id == 4) {
            int i = 1 / 0;
        }
    }

    /**************************************************************************************/
    /*                    方案二：每个线程单独的一个事务，互补影响                               */

    /**************************************************************************************/
    /*
    JDBC Connection [HikariProxyConnection@49411165 wrapping com.mysql.cj.jdbc.ConnectionImpl@7400f1d5] will be managed by Spring
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@63b1c569]
JDBC Connection [HikariProxyConnection@49411165 wrapping com.mysql.cj.jdbc.ConnectionImpl@7400f1d5] will be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
==> Parameters: 99(Integer)
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@63b1c569]
2023-08-02 22:31:08.607  INFO 13680 --- [nio-8081-exec-1] i.c.t.a.b.AccountLoginInfoInsertService  : (AccountLoginInfoInsertService.java:34) 新插入的ID为1
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@63b1c569]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@63b1c569]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@63b1c569]
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11dbc8de]
JDBC Connection [HikariProxyConnection@455320494 wrapping com.mysql.cj.jdbc.ConnectionImpl@7400f1d5] will be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
==> Parameters: 2(Integer)
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a15a8e4]
JDBC Connection [HikariProxyConnection@1894630292 wrapping com.mysql.cj.jdbc.ConnectionImpl@35ef53ed] will be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
==> Parameters: 3(Integer)
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11dbc8de]
2023-08-02 22:31:08.819  INFO 13680 --- [      Thread-11] i.c.t.a.b.AccountLoginInfoInsertService  : (AccountLoginInfoInsertService.java:34) 新插入的ID为1
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11dbc8de]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11dbc8de]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11dbc8de]
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a15a8e4]
2023-08-02 22:31:08.823  INFO 13680 --- [      Thread-12] i.c.t.a.b.AccountLoginInfoInsertService  : (AccountLoginInfoInsertService.java:34) 新插入的ID为1
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a15a8e4]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a15a8e4]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a15a8e4]
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4b03e0f3]
JDBC Connection [HikariProxyConnection@1107004291 wrapping com.mysql.cj.jdbc.ConnectionImpl@35ef53ed] will be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
==> Parameters: 4(Integer)
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4b03e0f3]
2023-08-02 22:31:08.995  INFO 13680 --- [      Thread-13] i.c.t.a.b.AccountLoginInfoInsertService  : (AccountLoginInfoInsertService.java:34) 新插入的ID为1
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4b03e0f3]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4b03e0f3]
Exception in thread "Thread-13" java.lang.ArithmeticException: / by zero
	at io.cloud.tec.ant_design_vue_pro.biz.AccountLoginInfoInsertService.insertOnlyPK(AccountLoginInfoInsertService.java:36)
	at io.cloud.tec.ant_design_vue_pro.biz.AccountLoginInfoInsertService$$FastClassBySpringCGLIB$$809c4cf1.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
	at io.cloud.tec.ant_design_vue_pro.biz.AccountLoginInfoInsertService$$EnhancerBySpringCGLIB$$e1ce9b17.insertOnlyPK(<generated>)
	at io.cloud.tec.ant_design_vue_pro.biz.AccountLoginInfoService.lambda$batchInsert$0(AccountLoginInfoService.java:134)
	at java.lang.Thread.run(Thread.java:748)
     */
    @Transactional
    public void batchInsert2() {
        cleanDataForTest();
        insertService.insertOnlyPK(99);
        for (int i = 2; i < 5; i++) {
            int finalI = i;
            Runnable runnable = () -> insertService.insertOnlyPK(finalI);

            new Thread(runnable).start();
        }
    }


    /**************************************************************************************/
    /*                    方案三：ThreadPoolTaskExecutor提交Runnable每个线程单独的一个事务，互不影响，日志和方案二一致                               */
    /**************************************************************************************/
    @Autowired
    SqlSessionFactory sqlSessionFactory;

    @Autowired
    AccountLoginInfoInsertService insertService;

    public void cleanDataForTest() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Connection connection = sqlSession.getConnection();
        try {
            Statement statement = connection.createStatement();
            statement.execute("delete from account_info where (id > 1 and id < 98) or id  = 99");
            statement.execute("update account_info set vn = vn + 1 where id = 1");
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }

    }

    @Transactional
    public void batchInsert3() {
        cleanDataForTest();
        insertService.insertOnlyPK(99);
        for (int i = 2; i < 5; i++) {
            int finalI = i;
            poolTaskExecutor.submit(() -> {
                insertService.insertOnlyPK(finalI);
            });
            System.out.println(i);
        }
    }

    @Autowired
    ThreadPoolTaskExecutor poolTaskExecutor;
    /**************************************************************************************/
    /*                    方案四：ThreadPoolTaskExecutor提交Callable每个线程单独的一个事务，互不影响，异常日志因为没有调用future.get()，所以没有输出 */

    /**************************************************************************************/
    /*
    JDBC Connection [HikariProxyConnection@1810957989 wrapping com.mysql.cj.jdbc.ConnectionImpl@661c058f] will be managed by Spring
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2acc0c9e]
JDBC Connection [HikariProxyConnection@1810957989 wrapping com.mysql.cj.jdbc.ConnectionImpl@661c058f] will be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
==> Parameters: 99(Integer)
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2acc0c9e]
2023-08-02 23:14:15.731  INFO 16092 --- [nio-8081-exec-5] i.c.t.a.b.AccountLoginInfoInsertService  : (AccountLoginInfoInsertService.java:34) 新插入的ID为1
2
3
4
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2acc0c9e]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2acc0c9e]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2acc0c9e]
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a1867b2]
JDBC Connection [HikariProxyConnection@622888135 wrapping com.mysql.cj.jdbc.ConnectionImpl@3366fb05] will be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
==> Parameters: 3(Integer)
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4ae62dc6]
JDBC Connection [HikariProxyConnection@1280393723 wrapping com.mysql.cj.jdbc.ConnectionImpl@7f64d65e] will be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
==> Parameters: 2(Integer)
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a1867b2]
2023-08-02 23:14:15.867  INFO 16092 --- [lTaskExecutor-2] i.c.t.a.b.AccountLoginInfoInsertService  : (AccountLoginInfoInsertService.java:34) 新插入的ID为1
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a1867b2]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a1867b2]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a1867b2]
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4ae62dc6]
2023-08-02 23:14:15.881  INFO 16092 --- [lTaskExecutor-1] i.c.t.a.b.AccountLoginInfoInsertService  : (AccountLoginInfoInsertService.java:34) 新插入的ID为1
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4ae62dc6]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4ae62dc6]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4ae62dc6]
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4ad1035d]
JDBC Connection [HikariProxyConnection@2024938889 wrapping com.mysql.cj.jdbc.ConnectionImpl@3366fb05] will be managed by Spring
==>  Preparing: INSERT INTO `account_info` (`id`) VALUES (?)
==> Parameters: 4(Integer)
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4ad1035d]
2023-08-02 23:14:16.042  INFO 16092 --- [lTaskExecutor-2] i.c.t.a.b.AccountLoginInfoInsertService  : (AccountLoginInfoInsertService.java:34) 新插入的ID为1
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4ad1035d]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4ad1035d]

     */
    @Transactional
    public void batchInsert() {
//        CountDownLatch countDownLatch = new CountDownLatch(1);
//        new Thread(() -> {
//            cleanDataForTest();
//            countDownLatch.countDown();
//        }).start();
//        try {
//            countDownLatch.await();
//        } catch (InterruptedException e) {
//            e.printStackTrace();
//        }
        // 直接调用cleanDataForTest，insertOnlyPK(99); 99会加锁，导致插入2超时
        cleanDataForTest();
        insertService.insertOnlyPK(99);
        for (int i = 2; i < 5; i++) {
            int finalI = i;
            Future<Integer> result = poolTaskExecutor.submit(() -> {
                insertService.insertOnlyPK(finalI);
                return finalI;
            });
            try {
                log.info("Future<Integer> result:{}", result.get());
            } catch (InterruptedException e) {
                e.printStackTrace();
            } catch (ExecutionException e) {
                e.printStackTrace();
            }
            System.out.println(i);
        }
    }
}
